﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using ClassFolio.Model;
using System.Data;
using ClassFolio;

namespace OraFolio.Gates
{
    public class OraSqlGate : ISqlGate
    {
        public void Open(Sequence sequence)
        {
            OraDB.CheckConnection();

            OracleCommand cmd = new OracleCommand(sequence.Sql, OraDB.Connection, OraDB.Transaction);
            sequence.Index = 0;
            sequence.SqlSwitch.Add("nmb", String.Format("{0},{1}", sequence.Index, sequence.RecordCount), 
                SqlCondition.btw, SqlType.sqlInt, Direction.input);

            GetParams(sequence, cmd);
            GetWhere(sequence, cmd);

            DbRead(sequence, cmd);
        }
        public void Next(Sequence sequence)
        {
            OraDB.CheckConnection();
            OracleCommand cmd = new OracleCommand(sequence.Sql, OraDB.Connection, OraDB.Transaction);

            sequence.SqlSwitch.Add("nmb", String.Format("{0},{1}", sequence.Index + sequence.RecordCount, 
                sequence.Index + 2 * sequence.RecordCount), SqlCondition.btw, SqlType.sqlInt, Direction.input);
            sequence.Index = sequence.Index + sequence.RecordCount;
            
            GetParams(sequence, cmd);
            GetWhere(sequence, cmd);

            DbRead(sequence, cmd);
        }

        public void Prior(Sequence sequence)
        {
            OraDB.CheckConnection();
            OracleCommand cmd = new OracleCommand(sequence.Sql, OraDB.Connection, OraDB.Transaction);

            sequence.SqlSwitch.Add("nmb", String.Format("{0},{1}", sequence.Index - sequence.RecordCount, 
                sequence.Index), SqlCondition.btw, SqlType.sqlInt, Direction.input);
            sequence.Index = sequence.Index - sequence.RecordCount;
            GetParams(sequence, cmd);
            GetWhere(sequence, cmd);

            DbRead(sequence, cmd);
        }

        // Private
        private static void DbRead(Sequence sequence, OracleCommand cmd)
        {
            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                sequence.Result.Clear();
                while (reader.Read())
                {
                    List<string> list = new List<string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                        list.Add(reader[i].ToString());

                    sequence.Result.Add(list);
                }
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.seq_refresh, err);
            }
            finally
            {
                reader.Close();
            }
        }
        private void GetParams(Sequence sequence, OracleCommand cmd)
        {
            foreach (SqlCase item in sequence.SqlSwitch.Condition)
            {
                switch (item.Dir)
                {
                    case Direction.input:
                        GetInputParams(cmd, item);
                        break;
                    case Direction.output:
                        cmd.Parameters.Add(item.Name, GetOraType(item.SqlType)).Direction = ParameterDirection.Output;
                        break;
                    default:
                        break;
                }
            }
        }
        private void GetWhere(Sequence sequence, OracleCommand cmd)
        {
            StringBuilder whereCase = new StringBuilder();
            foreach (SqlCase item in sequence.SqlSwitch.Condition)
            {
                switch (item.SqlCondition)
                {
                    case SqlCondition.equal:
                        whereCase.AppendFormat(" AND {0} = :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.not_eqaul:
                        whereCase.AppendFormat(" AND {0} <> :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.gt:
                        whereCase.AppendFormat(" AND {0} > :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.lt:
                        whereCase.AppendFormat(" AND {0} < :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.gte:
                        whereCase.AppendFormat(" AND {0} >= :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.lte:
                        whereCase.AppendFormat(" AND {0} <= :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.btw:
                        whereCase.AppendFormat(" AND {0} BETWEEN :{1} AND :{2}", item.Name,
                            GetParamName(item.Name, ParamNum.first), GetParamName(item.Name, ParamNum.second));
                        break;
                    case SqlCondition.like:
                        whereCase.AppendFormat(" AND {0} LIKE '%{1}%'", item.Name, item.Values);
                        //whereCase.AppendFormat(" AND {0} LIKE :{1}", item.Name, GetParamName(item.Name, ParamNum.first));
                        break;
                    case SqlCondition.in_enum:
                        if (item.SqlType == SqlType.sqlString || item.SqlType == SqlType.sqlDate)
                        {
                            var values = item.Values.Split(',');
                            string resValue = values.Aggregate((curr, next) =>
                                String.Format("'{0}'", curr.Trim()) + "," + String.Format("'{0}'", next.Trim()));
                            whereCase.AppendFormat(" AND {0} IN ({1})", item.Name, resValue);
                        }
                        else
                            whereCase.AppendFormat(" AND {0} IN ({1})", item.Name, item.Values);
                        break;
                    default:
                        whereCase.Append("");
                        break;
                }
            }
            cmd.CommandText = sequence.Sql + whereCase.ToString();
        }
        private void GetInputParams(OracleCommand cmd, SqlCase item)
        {
            string firstParam = GetParamName(item.Name, ParamNum.first);
            string secondParam = GetParamName(item.Name, ParamNum.second);
            
            switch (item.SqlCondition)
            {
                case SqlCondition.btw:
                    cmd.Parameters.Add(firstParam, GetOraType(item.SqlType)).Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(secondParam, GetOraType(item.SqlType)).Direction = ParameterDirection.Input;
                    string[] values = item.Values.Split(',');
                    if (values.Count() > 1)
                    {
                        cmd.Parameters[firstParam].Value = values[0];
                        cmd.Parameters[secondParam].Value = values[1];
                    }
                    else
                    {
                        cmd.Parameters[firstParam].Value = values[0];
                        cmd.Parameters[secondParam].Value = values[0];
                    }
                    break;        
                case SqlCondition.in_enum:
                    break;
                case SqlCondition.like:
                    //cmd.Parameters.Add(firstParam, GetOraType(item.SqlType)).Direction = ParameterDirection.Input;
                    //cmd.Parameters[firstParam].Value = String.Format("'%{0}%'", item.Values);
                    break;
                default:
                    cmd.Parameters.Add(firstParam, GetOraType(item.SqlType)).Direction = ParameterDirection.Input;
                    cmd.Parameters[firstParam].Value = item.Values;
                    break;
            }
        }
        private string GetParamName(string paramName, ParamNum paramNum)
        { 
            if (paramNum == ParamNum.second)
                return String.Format("p2_{0}", paramName);
            else
                return String.Format("p1_{0}", paramName);
        }

        private enum ParamNum { first, second }
        private OracleType GetOraType(SqlType sqlType)
        {
            switch (sqlType)
            {
                case SqlType.sqlInt:
                    return OracleType.Number;
                case SqlType.sqlString:
                    return OracleType.VarChar;
                case SqlType.sqlDate:
                    return OracleType.DateTime;
                case SqlType.sqlDouble:
                    return OracleType.Number;
                default:
                    throw new EFolioSource(0, MessageType.AllertLog, 
                        Messages.sq_convert_ora_type, null);
            }
        }
    }
}
